{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e8deae88",
   "metadata": {},
   "source": [
    "# 文件读写"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d2f60bb",
   "metadata": {},
   "source": [
    "## 修改路径：将路径转换到数据文件夹下"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "62b208b9",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "current path:  C:\\Users\\illus\\OneDrive\\A-Teaching\\Data-Analysis\\python-gitbook\n",
      "new path :  C:\\Users\\illus\\OneDrive\\A-Teaching\\Data-Analysis\\raw-data\n",
      "['baby_trade_history.csv', 'cfps2018_famconf_demo.csv', 'meal_order_detail.xlsx', 'sam_tianchi_mum_baby.csv', 'titanic.csv', 'titanic.xlsx']\n"
     ]
    }
   ],
   "source": [
    "# 导入os模块  \n",
    "import os\n",
    "\n",
    "# 查看当前路径\n",
    "print('current path: ', os.getcwd())   \n",
    "\n",
    "# 更改路径\n",
    "data_path = r'C:\\Users\\illus\\OneDrive\\A-Teaching\\Data-Analysis\\raw-data'  # 前面加r, 防止转义\n",
    "os.chdir(data_path)\n",
    "print('new path : ', os.getcwd()) \n",
    "print(os.listdir())  # 查看当前文件夹下文件"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b835e8c2",
   "metadata": {},
   "source": [
    "## 读取文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "4ffd2357",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1b497ae3",
   "metadata": {},
   "source": [
    "###  csv格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "id": "63e2d67f",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "df  = pd.read_csv('titanic.csv', dtype = {'PassengerId':str})   \n",
    "    # 结果为dataframe 格式； \n",
    "    # 可加选择\n",
    "        # encoding 默认为 utf-8; other 编码： gbk, gbk2312, gbk18030\n",
    "        # nrow = 100\n",
    "    \n",
    "#pd.read_csv?  # 查看方法说明"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "197cf3e0",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PassengerId</th>\n",
       "      <th>Survived</th>\n",
       "      <th>Pclass</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>SibSp</th>\n",
       "      <th>Parch</th>\n",
       "      <th>Ticket</th>\n",
       "      <th>Fare</th>\n",
       "      <th>Cabin</th>\n",
       "      <th>Embarked</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Braund, Mr. Owen Harris</td>\n",
       "      <td>male</td>\n",
       "      <td>22.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>A/5 21171</td>\n",
       "      <td>7.2500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Cumings, Mrs. John Bradley (Florence Briggs Th...</td>\n",
       "      <td>female</td>\n",
       "      <td>38.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>PC 17599</td>\n",
       "      <td>71.2833</td>\n",
       "      <td>C85</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Heikkinen, Miss. Laina</td>\n",
       "      <td>female</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>STON/O2. 3101282</td>\n",
       "      <td>7.9250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Futrelle, Mrs. Jacques Heath (Lily May Peel)</td>\n",
       "      <td>female</td>\n",
       "      <td>35.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>113803</td>\n",
       "      <td>53.1000</td>\n",
       "      <td>C123</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Allen, Mr. William Henry</td>\n",
       "      <td>male</td>\n",
       "      <td>35.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>373450</td>\n",
       "      <td>8.0500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  PassengerId  Survived  Pclass  \\\n",
       "0           1         0       3   \n",
       "1           2         1       1   \n",
       "2           3         1       3   \n",
       "3           4         1       1   \n",
       "4           5         0       3   \n",
       "\n",
       "                                                Name     Sex   Age  SibSp  \\\n",
       "0                            Braund, Mr. Owen Harris    male  22.0      1   \n",
       "1  Cumings, Mrs. John Bradley (Florence Briggs Th...  female  38.0      1   \n",
       "2                             Heikkinen, Miss. Laina  female  26.0      0   \n",
       "3       Futrelle, Mrs. Jacques Heath (Lily May Peel)  female  35.0      1   \n",
       "4                           Allen, Mr. William Henry    male  35.0      0   \n",
       "\n",
       "   Parch            Ticket     Fare Cabin Embarked  \n",
       "0      0         A/5 21171   7.2500   NaN        S  \n",
       "1      0          PC 17599  71.2833   C85        C  \n",
       "2      0  STON/O2. 3101282   7.9250   NaN        S  \n",
       "3      0            113803  53.1000  C123        S  \n",
       "4      0            373450   8.0500   NaN        S  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看数据\n",
    "df.head() #默认为前5行\n",
    "# df.tail()  #默认为倒数5行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "1b476f6f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 891 entries, 0 to 890\n",
      "Data columns (total 12 columns):\n",
      " #   Column       Non-Null Count  Dtype  \n",
      "---  ------       --------------  -----  \n",
      " 0   PassengerId  891 non-null    object \n",
      " 1   Survived     891 non-null    int64  \n",
      " 2   Pclass       891 non-null    int64  \n",
      " 3   Name         891 non-null    object \n",
      " 4   Sex          891 non-null    object \n",
      " 5   Age          714 non-null    float64\n",
      " 6   SibSp        891 non-null    int64  \n",
      " 7   Parch        891 non-null    int64  \n",
      " 8   Ticket       891 non-null    object \n",
      " 9   Fare         891 non-null    float64\n",
      " 10  Cabin        204 non-null    object \n",
      " 11  Embarked     889 non-null    object \n",
      "dtypes: float64(2), int64(4), object(6)\n",
      "memory usage: 83.7+ KB\n"
     ]
    }
   ],
   "source": [
    "# 获取数据基本信息\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "446c6f0d",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Survived</th>\n",
       "      <th>Pclass</th>\n",
       "      <th>Age</th>\n",
       "      <th>SibSp</th>\n",
       "      <th>Parch</th>\n",
       "      <th>Fare</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>891.000000</td>\n",
       "      <td>891.000000</td>\n",
       "      <td>714.000000</td>\n",
       "      <td>891.000000</td>\n",
       "      <td>891.000000</td>\n",
       "      <td>891.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>0.383838</td>\n",
       "      <td>2.308642</td>\n",
       "      <td>29.699118</td>\n",
       "      <td>0.523008</td>\n",
       "      <td>0.381594</td>\n",
       "      <td>32.204208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.486592</td>\n",
       "      <td>0.836071</td>\n",
       "      <td>14.526497</td>\n",
       "      <td>1.102743</td>\n",
       "      <td>0.806057</td>\n",
       "      <td>49.693429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.420000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>20.125000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>7.910400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>28.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>14.454200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>38.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>31.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>80.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>512.329200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Survived      Pclass         Age       SibSp       Parch        Fare\n",
       "count  891.000000  891.000000  714.000000  891.000000  891.000000  891.000000\n",
       "mean     0.383838    2.308642   29.699118    0.523008    0.381594   32.204208\n",
       "std      0.486592    0.836071   14.526497    1.102743    0.806057   49.693429\n",
       "min      0.000000    1.000000    0.420000    0.000000    0.000000    0.000000\n",
       "25%      0.000000    2.000000   20.125000    0.000000    0.000000    7.910400\n",
       "50%      0.000000    3.000000   28.000000    0.000000    0.000000   14.454200\n",
       "75%      1.000000    3.000000   38.000000    1.000000    0.000000   31.000000\n",
       "max      1.000000    3.000000   80.000000    8.000000    6.000000  512.329200"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 获取数值数据的描述性分析\n",
    "df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2cf89987",
   "metadata": {},
   "source": [
    "### Excel 格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "8b390ca9",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "df = pd.read_excel('titanic.xlsx',sheet_name = 'titanic', dtype = {'PassengerId':str})\n",
    "\n",
    "# pd.read_excel?\n",
    "# df.head()\n",
    "# df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "64b03818",
   "metadata": {},
   "source": [
    "### dta格式 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "9a790bdc",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "file_path_famconf = r'C:\\\\Users\\\\illus\\\\OneDrive\\\\B-Database\\\\中国家庭追踪调查-data\\\\CFPS-data\\\\ecfps2018famconf_202008.dta'\n",
    "data_famconf = pd.read_stata(file_path_famconf, convert_categoricals=False) \n",
    "#如果报错：value labels for column wm703 are not unique.解决方法  convert_categoricals=False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "3d956957",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(58504, 296)"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_famconf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "49e3db37",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fid18</th>\n",
       "      <th>fid_provcd18</th>\n",
       "      <th>fid_countyid18</th>\n",
       "      <th>fid_cid18</th>\n",
       "      <th>fid_urban18</th>\n",
       "      <th>pid</th>\n",
       "      <th>fid_base</th>\n",
       "      <th>psu</th>\n",
       "      <th>fid10</th>\n",
       "      <th>fid12</th>\n",
       "      <th>...</th>\n",
       "      <th>ads1_18</th>\n",
       "      <th>kz103_18</th>\n",
       "      <th>interrupt18</th>\n",
       "      <th>sresppid18</th>\n",
       "      <th>kz1pid18</th>\n",
       "      <th>cyear18</th>\n",
       "      <th>cmonth18</th>\n",
       "      <th>iwmode18</th>\n",
       "      <th>interviewerid18</th>\n",
       "      <th>releaseversion</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100051.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>624942.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>100051501.0</td>\n",
       "      <td>110043.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>-8.0</td>\n",
       "      <td>-8.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100051502.0</td>\n",
       "      <td>100051502.0</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>761040.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100051.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>624942.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>110043107.0</td>\n",
       "      <td>110043.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>110043.0</td>\n",
       "      <td>110043.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100051502.0</td>\n",
       "      <td>100051502.0</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>761040.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100051.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>624942.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>100051502.0</td>\n",
       "      <td>110043.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>-8.0</td>\n",
       "      <td>-8.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100051502.0</td>\n",
       "      <td>100051502.0</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>761040.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100160.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>-9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>100160601.0</td>\n",
       "      <td>120009.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>-8.0</td>\n",
       "      <td>-8.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>120009102.0</td>\n",
       "      <td>120009102.0</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>459505.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100160.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>-9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>120009102.0</td>\n",
       "      <td>120009.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>120009.0</td>\n",
       "      <td>120009.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>120009102.0</td>\n",
       "      <td>120009102.0</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>459505.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 296 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      fid18  fid_provcd18  fid_countyid18  fid_cid18  fid_urban18  \\\n",
       "0  100051.0          11.0            45.0   624942.0          1.0   \n",
       "1  100051.0          11.0            45.0   624942.0          1.0   \n",
       "2  100051.0          11.0            45.0   624942.0          1.0   \n",
       "3  100160.0          12.0            79.0       -9.0          1.0   \n",
       "4  100160.0          12.0            79.0       -9.0          1.0   \n",
       "\n",
       "           pid  fid_base   psu     fid10     fid12  ...  ads1_18  kz103_18  \\\n",
       "0  100051501.0  110043.0  45.0      -8.0      -8.0  ...      0.0       1.0   \n",
       "1  110043107.0  110043.0  45.0  110043.0  110043.0  ...      0.0       1.0   \n",
       "2  100051502.0  110043.0  45.0      -8.0      -8.0  ...      0.0       1.0   \n",
       "3  100160601.0  120009.0  79.0      -8.0      -8.0  ...      0.0       1.0   \n",
       "4  120009102.0  120009.0  79.0  120009.0  120009.0  ...      0.0       1.0   \n",
       "\n",
       "   interrupt18   sresppid18     kz1pid18  cyear18  cmonth18  iwmode18  \\\n",
       "0          0.0  100051502.0  100051502.0   2018.0      10.0       2.0   \n",
       "1          0.0  100051502.0  100051502.0   2018.0      10.0       2.0   \n",
       "2          0.0  100051502.0  100051502.0   2018.0      10.0       2.0   \n",
       "3          0.0  120009102.0  120009102.0   2018.0       8.0       1.0   \n",
       "4          0.0  120009102.0  120009102.0   2018.0       8.0       1.0   \n",
       "\n",
       "   interviewerid18  releaseversion  \n",
       "0         761040.0             1.0  \n",
       "1         761040.0             1.0  \n",
       "2         761040.0             1.0  \n",
       "3         459505.0             1.0  \n",
       "4         459505.0             1.0  \n",
       "\n",
       "[5 rows x 296 columns]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# data_famconf.head()\n",
    "data_famconf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "703762b0",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# pd.set_option('display.max_columns',20)\n",
    "# pd.set_option('display.max_rows',100)\n",
    "# 这里设定无效。。。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc5b7a71",
   "metadata": {},
   "source": [
    "## 保存"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de942acc",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# 保存为csv\n",
    "data_save_path = r'C:\\Users\\illus\\OneDrive\\A-Teaching\\Data-Analysis\\data'\n",
    "df.to_csv(os.path.join(data_save_path,'titanic_adjust.csv'),index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "a3188856",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# 保存为excel 格式\n",
    "df.to_excel(os.path.join(data_save_path,'titanic_adjust.xlsx'),index=False, sheet_name = 'titanic')\n",
    "\n",
    "# DataFrame.to_excel(excel_writer, sheet_name='Sheet1', na_rep='',  float_format=None, columns=None, header=True, index=True, \n",
    "# index_label=None, startrow=0, startcol=0, engine=None,  merge_cells=True, encoding=None, inf_rep='inf', verbose=True, \n",
    "# freeze_panes=None)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0936a78",
   "metadata": {},
   "source": [
    "# 数据表处理"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a5a6fd0",
   "metadata": {},
   "source": [
    "## 数值查看和获取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "id": "0de4be11",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['PassengerId', 'Survived', 'Pclass', 'Name', 'Sex', 'Age', 'SibSp',\n",
      "       'Parch', 'Ticket', 'Fare', 'Cabin', 'Embarked'],\n",
      "      dtype='object')\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 891 entries, 0 to 890\n",
      "Data columns (total 12 columns):\n",
      " #   Column       Non-Null Count  Dtype  \n",
      "---  ------       --------------  -----  \n",
      " 0   PassengerId  891 non-null    object \n",
      " 1   Survived     891 non-null    int64  \n",
      " 2   Pclass       891 non-null    int64  \n",
      " 3   Name         891 non-null    object \n",
      " 4   Sex          891 non-null    object \n",
      " 5   Age          714 non-null    float64\n",
      " 6   SibSp        891 non-null    int64  \n",
      " 7   Parch        891 non-null    int64  \n",
      " 8   Ticket       891 non-null    object \n",
      " 9   Fare         891 non-null    float64\n",
      " 10  Cabin        204 non-null    object \n",
      " 11  Embarked     889 non-null    object \n",
      "dtypes: float64(2), int64(4), object(6)\n",
      "memory usage: 83.7+ KB\n",
      "None\n",
      "         Survived      Pclass         Age       SibSp       Parch        Fare\n",
      "count  891.000000  891.000000  714.000000  891.000000  891.000000  891.000000\n",
      "mean     0.383838    2.308642   29.699118    0.523008    0.381594   32.204208\n",
      "std      0.486592    0.836071   14.526497    1.102743    0.806057   49.693429\n",
      "min      0.000000    1.000000    0.420000    0.000000    0.000000    0.000000\n",
      "25%      0.000000    2.000000   20.125000    0.000000    0.000000    7.910400\n",
      "50%      0.000000    3.000000   28.000000    0.000000    0.000000   14.454200\n",
      "75%      1.000000    3.000000   38.000000    1.000000    0.000000   31.000000\n",
      "max      1.000000    3.000000   80.000000    8.000000    6.000000  512.329200\n",
      "Index(['Survived', 'Pclass', 'Age', 'SibSp', 'Parch', 'Fare'], dtype='object')\n"
     ]
    }
   ],
   "source": [
    "# 导入数据\n",
    "data_path = r'C:\\Users\\illus\\OneDrive\\A-Teaching\\Data-Analysis\\raw-data'  # 前面加r, 防止转义\n",
    "os.chdir(data_path)\n",
    "df = pd.read_excel('titanic.xlsx',sheet_name = 'titanic', dtype = {'PassengerId':str})\n",
    "\n",
    "# 查看数据\n",
    "df.head()\n",
    "print(df.columns)\n",
    "# 确认变量含义，如果变量很多，确认需要使用的变量\n",
    "\n",
    "\n",
    "print(df.info())\n",
    "print(df.describe()) \n",
    "\n",
    "print(df.describe().columns)  #quick way to separate numeric columns\n",
    "# or by \n",
    "#df.dtypes[df.dtypes == 'int64']  # 提取int"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 268,
   "id": "eb92fbaf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PassengerId</th>\n",
       "      <th>Survived</th>\n",
       "      <th>Sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  PassengerId  Survived     Sex\n",
       "0           1         0    male\n",
       "1           2         1  female\n",
       "2           3         1  female\n",
       "3           4         1  female\n",
       "4           5         0    male\n",
       "5           6         0    male\n",
       "6           7         0    male\n",
       "7           8         0    male\n",
       "8           9         1  female\n",
       "9          10         1  female"
      ]
     },
     "execution_count": 268,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 获取数值\n",
    "'''\n",
    "基础索引：df[]\n",
    "iloc: 使用位置\n",
    "loc: 使用索引\n",
    "'''\n",
    "\n",
    "df[['PassengerId','Survived','Sex']][0:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dbc9a3eb",
   "metadata": {},
   "source": [
    "## 增、删、改、查找"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "267af230",
   "metadata": {},
   "source": [
    "### 增"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4192796",
   "metadata": {},
   "source": [
    "#### 增加列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2ba38001",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 增加数据\n",
    "df2 = df.copy() # 复制一份数据\n",
    "\n",
    "# 增加1列\n",
    "df2['new_variable'] = list(range(891))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 279,
   "id": "f99fbc44",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PassengerId</th>\n",
       "      <th>Survived</th>\n",
       "      <th>Pclass</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>SibSp</th>\n",
       "      <th>Parch</th>\n",
       "      <th>Ticket</th>\n",
       "      <th>Fare</th>\n",
       "      <th>Cabin</th>\n",
       "      <th>Embarked</th>\n",
       "      <th>new_variable</th>\n",
       "      <th>sex_num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Braund, Mr. Owen Harris</td>\n",
       "      <td>male</td>\n",
       "      <td>22.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>A/5 21171</td>\n",
       "      <td>7.2500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Cumings, Mrs. John Bradley (Florence Briggs Th...</td>\n",
       "      <td>female</td>\n",
       "      <td>38.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>PC 17599</td>\n",
       "      <td>71.2833</td>\n",
       "      <td>C85</td>\n",
       "      <td>C</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Heikkinen, Miss. Laina</td>\n",
       "      <td>female</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>STON/O2. 3101282</td>\n",
       "      <td>7.9250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  PassengerId  Survived  Pclass  \\\n",
       "0           1         0       3   \n",
       "1           2         1       1   \n",
       "2           3         1       3   \n",
       "\n",
       "                                                Name     Sex   Age  SibSp  \\\n",
       "0                            Braund, Mr. Owen Harris    male  22.0      1   \n",
       "1  Cumings, Mrs. John Bradley (Florence Briggs Th...  female  38.0      1   \n",
       "2                             Heikkinen, Miss. Laina  female  26.0      0   \n",
       "\n",
       "   Parch            Ticket     Fare Cabin Embarked  new_variable  sex_num  \n",
       "0      0         A/5 21171   7.2500   NaN        S             0        0  \n",
       "1      0          PC 17599  71.2833   C85        C             1        1  \n",
       "2      0  STON/O2. 3101282   7.9250   NaN        S             2        1  "
      ]
     },
     "execution_count": 279,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2['sex_num'] = np.where(df2['Sex'] == 'male', 0, 1)\n",
    "df2.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 281,
   "id": "1a3985c3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PassengerId</th>\n",
       "      <th>Survived</th>\n",
       "      <th>Pclass</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>SibSp</th>\n",
       "      <th>Parch</th>\n",
       "      <th>Ticket</th>\n",
       "      <th>Fare</th>\n",
       "      <th>Cabin</th>\n",
       "      <th>Embarked</th>\n",
       "      <th>new_variable</th>\n",
       "      <th>sex_num</th>\n",
       "      <th>sex_num2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Braund, Mr. Owen Harris</td>\n",
       "      <td>male</td>\n",
       "      <td>22.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>A/5 21171</td>\n",
       "      <td>7.2500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Cumings, Mrs. John Bradley (Florence Briggs Th...</td>\n",
       "      <td>female</td>\n",
       "      <td>38.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>PC 17599</td>\n",
       "      <td>71.2833</td>\n",
       "      <td>C85</td>\n",
       "      <td>C</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Heikkinen, Miss. Laina</td>\n",
       "      <td>female</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>STON/O2. 3101282</td>\n",
       "      <td>7.9250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  PassengerId  Survived  Pclass  \\\n",
       "0           1         0       3   \n",
       "1           2         1       1   \n",
       "2           3         1       3   \n",
       "\n",
       "                                                Name     Sex   Age  SibSp  \\\n",
       "0                            Braund, Mr. Owen Harris    male  22.0      1   \n",
       "1  Cumings, Mrs. John Bradley (Florence Briggs Th...  female  38.0      1   \n",
       "2                             Heikkinen, Miss. Laina  female  26.0      0   \n",
       "\n",
       "   Parch            Ticket     Fare Cabin Embarked  new_variable  sex_num  \\\n",
       "0      0         A/5 21171   7.2500   NaN        S             0        0   \n",
       "1      0          PC 17599  71.2833   C85        C             1        1   \n",
       "2      0  STON/O2. 3101282   7.9250   NaN        S             2        1   \n",
       "\n",
       "   sex_num2  \n",
       "0         0  \n",
       "1         1  \n",
       "2         1  "
      ]
     },
     "execution_count": 281,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2['sex_num2'] = df2['Sex'].map(lambda x: 0 if x == 'male' else 1)\n",
    "df2.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b001c042",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 指定位置添加列；注意：列名(不能与存在列名相同)\n",
    "\n",
    "df2.insert(2,'new_variable',range(891))  # 位置，新的变量名，变量\n",
    "#df2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f12dee66",
   "metadata": {},
   "source": [
    "#### 增加行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 316,
   "id": "b0a8828c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PassengerId</th>\n",
       "      <th>Survived</th>\n",
       "      <th>new_variable3</th>\n",
       "      <th>Pclass</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>SibSp</th>\n",
       "      <th>Parch</th>\n",
       "      <th>Ticket</th>\n",
       "      <th>Fare</th>\n",
       "      <th>Cabin</th>\n",
       "      <th>Embarked</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>891</th>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>Nasser, Mrs. Nicholas (Adele Achem)</td>\n",
       "      <td>female</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>237736</td>\n",
       "      <td>30.0708</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>892</th>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>3</td>\n",
       "      <td>Sandstrom, Miss. Marguerite Rut</td>\n",
       "      <td>female</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>PP 9549</td>\n",
       "      <td>16.7000</td>\n",
       "      <td>G6</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>893</th>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>Bonnell, Miss. Elizabeth</td>\n",
       "      <td>female</td>\n",
       "      <td>58.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>113783</td>\n",
       "      <td>26.5500</td>\n",
       "      <td>C103</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    PassengerId  Survived  new_variable3  Pclass  \\\n",
       "891          10         1              9       2   \n",
       "892          11         1             10       3   \n",
       "893          12         1             11       1   \n",
       "\n",
       "                                    Name     Sex   Age  SibSp  Parch   Ticket  \\\n",
       "891  Nasser, Mrs. Nicholas (Adele Achem)  female  14.0      1      0   237736   \n",
       "892      Sandstrom, Miss. Marguerite Rut  female   4.0      1      1  PP 9549   \n",
       "893             Bonnell, Miss. Elizabeth  female  58.0      0      0   113783   \n",
       "\n",
       "        Fare Cabin Embarked  \n",
       "891  30.0708   NaN        C  \n",
       "892  16.7000    G6        S  \n",
       "893  26.5500  C103        S  "
      ]
     },
     "execution_count": 316,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# use append\n",
    "new = df2.iloc[9:12,:]\n",
    "df3 = df2.append(new,ignore_index=True)\n",
    "# True 按照新的数组的行索引排列\n",
    "# False 保持原有的 \n",
    "df2.shape, df3.shape\n",
    "df3.tail(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 317,
   "id": "e95c2412",
   "metadata": {},
   "outputs": [],
   "source": [
    "df3 = pd.DataFrame(columns = df2.columns)\n",
    "df3 = df3.append(new,ignore_index = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b91984b8",
   "metadata": {},
   "source": [
    "### 删"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b21e1ccf",
   "metadata": {},
   "source": [
    "#### 删除列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 299,
   "id": "cc817677",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['PassengerId', 'Survived', 'new_variable3', 'new_variable2', 'Pclass',\n",
       "       'Name', 'Sex', 'Age', 'SibSp', 'Parch', 'Ticket', 'Fare', 'Cabin',\n",
       "       'Embarked', 'new_variable', 'sex_num', 'sex_num2'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 299,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 310,
   "id": "78c87f47",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['PassengerId', 'Survived', 'new_variable3', 'Pclass', 'Name', 'Sex',\n",
       "       'Age', 'SibSp', 'Parch', 'Ticket', 'Fare', 'Cabin', 'Embarked'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 310,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.drop(labels = ['new_variable','new_variable2','sex_num','sex_num2'], axis = 1, inplace = True)\n",
    "df3.columns\n",
    "\n",
    "# 同时删除多个变量，需要以列表的形式\n",
    "# inplace =True,代表是否对原数据操作, 否则返回的是视图，并没有对原数据进行操作\n",
    "# axis = 1代表删除列； axis = 0 表示删除行；默认 axis = 0"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ffa5732a",
   "metadata": {},
   "source": [
    "#### 删除行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 320,
   "id": "cf5d1046",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PassengerId</th>\n",
       "      <th>Survived</th>\n",
       "      <th>new_variable3</th>\n",
       "      <th>Pclass</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>SibSp</th>\n",
       "      <th>Parch</th>\n",
       "      <th>Ticket</th>\n",
       "      <th>Fare</th>\n",
       "      <th>Cabin</th>\n",
       "      <th>Embarked</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>3</td>\n",
       "      <td>Sandstrom, Miss. Marguerite Rut</td>\n",
       "      <td>female</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>PP 9549</td>\n",
       "      <td>16.70</td>\n",
       "      <td>G6</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>Bonnell, Miss. Elizabeth</td>\n",
       "      <td>female</td>\n",
       "      <td>58.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>113783</td>\n",
       "      <td>26.55</td>\n",
       "      <td>C103</td>\n",
       "      <td>S</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  PassengerId Survived new_variable3 Pclass                             Name  \\\n",
       "1          11        1            10      3  Sandstrom, Miss. Marguerite Rut   \n",
       "2          12        1            11      1         Bonnell, Miss. Elizabeth   \n",
       "\n",
       "      Sex   Age SibSp Parch   Ticket   Fare Cabin Embarked  \n",
       "1  female   4.0     1     1  PP 9549  16.70    G6        S  \n",
       "2  female  58.0     0     0   113783  26.55  C103        S  "
      ]
     },
     "execution_count": 320,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.drop(labels = 0,axis = 0, inplace = True)\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f27a132c",
   "metadata": {},
   "source": [
    "### 修改"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b54b0e8",
   "metadata": {},
   "source": [
    "#### 修改列名、行索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 324,
   "id": "5fec6481",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PassengerId</th>\n",
       "      <th>Survived</th>\n",
       "      <th>new_variable3</th>\n",
       "      <th>Pclass</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>SibSp</th>\n",
       "      <th>Parch</th>\n",
       "      <th>Ticket</th>\n",
       "      <th>Fare</th>\n",
       "      <th>Cabin</th>\n",
       "      <th>Embarked</th>\n",
       "      <th>sex2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>Braund, Mr. Owen Harris</td>\n",
       "      <td>male</td>\n",
       "      <td>22.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>A/5 21171</td>\n",
       "      <td>7.2500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Cumings, Mrs. John Bradley (Florence Briggs Th...</td>\n",
       "      <td>female</td>\n",
       "      <td>38.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>PC 17599</td>\n",
       "      <td>71.2833</td>\n",
       "      <td>C85</td>\n",
       "      <td>C</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>Heikkinen, Miss. Laina</td>\n",
       "      <td>female</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>STON/O2. 3101282</td>\n",
       "      <td>7.9250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  PassengerId  Survived  new_variable3  Pclass  \\\n",
       "0           1         0              0       3   \n",
       "1           2         1              1       1   \n",
       "2           3         1              2       3   \n",
       "\n",
       "                                                Name     Sex   Age  SibSp  \\\n",
       "0                            Braund, Mr. Owen Harris    male  22.0      1   \n",
       "1  Cumings, Mrs. John Bradley (Florence Briggs Th...  female  38.0      1   \n",
       "2                             Heikkinen, Miss. Laina  female  26.0      0   \n",
       "\n",
       "   Parch            Ticket     Fare Cabin Embarked sex2  \n",
       "0      0         A/5 21171   7.2500   NaN        S   男性  \n",
       "1      0          PC 17599  71.2833   C85        C   女性  \n",
       "2      0  STON/O2. 3101282   7.9250   NaN        S   女性  "
      ]
     },
     "execution_count": 324,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df.columns = new_col\n",
    "# df.index = new_index\n",
    "\n",
    "# df.rename(index = {}, columns = {}, inplace = True)\n",
    "\n",
    "# 修改数据\n",
    "# df['col'] = [] #修改列\n",
    "# df[0] = []  # 修改行\n",
    "\n",
    "# 将Sex为female的改为女性，male改为男性\n",
    "df2.loc[df2['Sex'] == 'female','sex2'] = '女性'\n",
    "df2.loc[df2['Sex'] == 'male','sex2'] = '男性'\n",
    "df2.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 325,
   "id": "5465ca6a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['male', 'female'], dtype=object)"
      ]
     },
     "execution_count": 325,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check unique values of Sex in case\n",
    "df2.Sex.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70e597fb",
   "metadata": {},
   "source": [
    "### 查找"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb91bf8c",
   "metadata": {},
   "source": [
    "#### 条件查询"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 327,
   "id": "c8c94880",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PassengerId</th>\n",
       "      <th>Survived</th>\n",
       "      <th>new_variable3</th>\n",
       "      <th>Pclass</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>SibSp</th>\n",
       "      <th>Parch</th>\n",
       "      <th>Ticket</th>\n",
       "      <th>Fare</th>\n",
       "      <th>Cabin</th>\n",
       "      <th>Embarked</th>\n",
       "      <th>sex2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Cumings, Mrs. John Bradley (Florence Briggs Th...</td>\n",
       "      <td>female</td>\n",
       "      <td>38.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>PC 17599</td>\n",
       "      <td>71.2833</td>\n",
       "      <td>C85</td>\n",
       "      <td>C</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>Heikkinen, Miss. Laina</td>\n",
       "      <td>female</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>STON/O2. 3101282</td>\n",
       "      <td>7.9250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Futrelle, Mrs. Jacques Heath (Lily May Peel)</td>\n",
       "      <td>female</td>\n",
       "      <td>35.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>113803</td>\n",
       "      <td>53.1000</td>\n",
       "      <td>C123</td>\n",
       "      <td>S</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>Johnson, Mrs. Oscar W (Elisabeth Vilhelmina Berg)</td>\n",
       "      <td>female</td>\n",
       "      <td>27.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>347742</td>\n",
       "      <td>11.1333</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>Nasser, Mrs. Nicholas (Adele Achem)</td>\n",
       "      <td>female</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>237736</td>\n",
       "      <td>30.0708</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>880</th>\n",
       "      <td>881</td>\n",
       "      <td>1</td>\n",
       "      <td>880</td>\n",
       "      <td>2</td>\n",
       "      <td>Shelley, Mrs. William (Imanita Parrish Hall)</td>\n",
       "      <td>female</td>\n",
       "      <td>25.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>230433</td>\n",
       "      <td>26.0000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>882</th>\n",
       "      <td>883</td>\n",
       "      <td>0</td>\n",
       "      <td>882</td>\n",
       "      <td>3</td>\n",
       "      <td>Dahlberg, Miss. Gerda Ulrika</td>\n",
       "      <td>female</td>\n",
       "      <td>22.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7552</td>\n",
       "      <td>10.5167</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>885</th>\n",
       "      <td>886</td>\n",
       "      <td>0</td>\n",
       "      <td>885</td>\n",
       "      <td>3</td>\n",
       "      <td>Rice, Mrs. William (Margaret Norton)</td>\n",
       "      <td>female</td>\n",
       "      <td>39.0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>382652</td>\n",
       "      <td>29.1250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Q</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>887</th>\n",
       "      <td>888</td>\n",
       "      <td>1</td>\n",
       "      <td>887</td>\n",
       "      <td>1</td>\n",
       "      <td>Graham, Miss. Margaret Edith</td>\n",
       "      <td>female</td>\n",
       "      <td>19.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>112053</td>\n",
       "      <td>30.0000</td>\n",
       "      <td>B42</td>\n",
       "      <td>S</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>888</th>\n",
       "      <td>889</td>\n",
       "      <td>0</td>\n",
       "      <td>888</td>\n",
       "      <td>3</td>\n",
       "      <td>Johnston, Miss. Catherine Helen \"Carrie\"</td>\n",
       "      <td>female</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>W./C. 6607</td>\n",
       "      <td>23.4500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>314 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    PassengerId  Survived  new_variable3  Pclass  \\\n",
       "1             2         1              1       1   \n",
       "2             3         1              2       3   \n",
       "3             4         1              3       1   \n",
       "8             9         1              8       3   \n",
       "9            10         1              9       2   \n",
       "..          ...       ...            ...     ...   \n",
       "880         881         1            880       2   \n",
       "882         883         0            882       3   \n",
       "885         886         0            885       3   \n",
       "887         888         1            887       1   \n",
       "888         889         0            888       3   \n",
       "\n",
       "                                                  Name     Sex   Age  SibSp  \\\n",
       "1    Cumings, Mrs. John Bradley (Florence Briggs Th...  female  38.0      1   \n",
       "2                               Heikkinen, Miss. Laina  female  26.0      0   \n",
       "3         Futrelle, Mrs. Jacques Heath (Lily May Peel)  female  35.0      1   \n",
       "8    Johnson, Mrs. Oscar W (Elisabeth Vilhelmina Berg)  female  27.0      0   \n",
       "9                  Nasser, Mrs. Nicholas (Adele Achem)  female  14.0      1   \n",
       "..                                                 ...     ...   ...    ...   \n",
       "880       Shelley, Mrs. William (Imanita Parrish Hall)  female  25.0      0   \n",
       "882                       Dahlberg, Miss. Gerda Ulrika  female  22.0      0   \n",
       "885               Rice, Mrs. William (Margaret Norton)  female  39.0      0   \n",
       "887                       Graham, Miss. Margaret Edith  female  19.0      0   \n",
       "888           Johnston, Miss. Catherine Helen \"Carrie\"  female   NaN      1   \n",
       "\n",
       "     Parch            Ticket     Fare Cabin Embarked sex2  \n",
       "1        0          PC 17599  71.2833   C85        C   女性  \n",
       "2        0  STON/O2. 3101282   7.9250   NaN        S   女性  \n",
       "3        0            113803  53.1000  C123        S   女性  \n",
       "8        2            347742  11.1333   NaN        S   女性  \n",
       "9        0            237736  30.0708   NaN        C   女性  \n",
       "..     ...               ...      ...   ...      ...  ...  \n",
       "880      1            230433  26.0000   NaN        S   女性  \n",
       "882      0              7552  10.5167   NaN        S   女性  \n",
       "885      5            382652  29.1250   NaN        Q   女性  \n",
       "887      0            112053  30.0000   B42        S   女性  \n",
       "888      2        W./C. 6607  23.4500   NaN        S   女性  \n",
       "\n",
       "[314 rows x 14 columns]"
      ]
     },
     "execution_count": 327,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看女性数据\n",
    "df2[df2['Sex'] == 'female']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 330,
   "id": "9660cb33",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1      1\n",
       "2      1\n",
       "3      1\n",
       "8      1\n",
       "9      1\n",
       "      ..\n",
       "880    1\n",
       "882    0\n",
       "885    0\n",
       "887    1\n",
       "888    0\n",
       "Name: Survived, Length: 314, dtype: int64"
      ]
     },
     "execution_count": 330,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df2[df2['Sex'] == 'female']['Survived']\n",
    "df2.loc[df2['Sex'] == 'female','Survived']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 335,
   "id": "1c57a19d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Age</th>\n",
       "      <th>Survived</th>\n",
       "      <th>Sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>14.0</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>15.0</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>14.0</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>855</th>\n",
       "      <td>18.0</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>858</th>\n",
       "      <td>24.0</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>869</th>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>875</th>\n",
       "      <td>15.0</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>887</th>\n",
       "      <td>19.0</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>123 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Age  Survived     Sex\n",
       "9    14.0         1  female\n",
       "10    4.0         1  female\n",
       "22   15.0         1  female\n",
       "39   14.0         1  female\n",
       "43    3.0         1  female\n",
       "..    ...       ...     ...\n",
       "855  18.0         1  female\n",
       "858  24.0         1  female\n",
       "869   4.0         1    male\n",
       "875  15.0         1  female\n",
       "887  19.0         1  female\n",
       "\n",
       "[123 rows x 3 columns]"
      ]
     },
     "execution_count": 335,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看 Age < 25 和 Age > 60 的数据\n",
    "df2.loc[((df2['Age']<25) | (df2['Age']>60))& (df2['Survived'] == 1) ,['Age','Survived','Sex']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e3e4a55b",
   "metadata": {},
   "source": [
    "#### 使用between() 和 isin()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 342,
   "id": "fb5a20fe",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(17, 14)"
      ]
     },
     "execution_count": 342,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# between()\n",
    "df2[ df2['Age'].between(4,6)].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 339,
   "id": "8a6659cb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PassengerId</th>\n",
       "      <th>Survived</th>\n",
       "      <th>new_variable3</th>\n",
       "      <th>Pclass</th>\n",
       "      <th>Name</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Age</th>\n",
       "      <th>SibSp</th>\n",
       "      <th>Parch</th>\n",
       "      <th>Ticket</th>\n",
       "      <th>Fare</th>\n",
       "      <th>Cabin</th>\n",
       "      <th>Embarked</th>\n",
       "      <th>sex2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>3</td>\n",
       "      <td>Sandstrom, Miss. Marguerite Rut</td>\n",
       "      <td>female</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>PP 9549</td>\n",
       "      <td>16.7000</td>\n",
       "      <td>G6</td>\n",
       "      <td>S</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>23</td>\n",
       "      <td>1</td>\n",
       "      <td>Sloper, Mr. William Thompson</td>\n",
       "      <td>male</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>113788</td>\n",
       "      <td>35.5000</td>\n",
       "      <td>A6</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>35</td>\n",
       "      <td>0</td>\n",
       "      <td>34</td>\n",
       "      <td>1</td>\n",
       "      <td>Meyer, Mr. Edgar Joseph</td>\n",
       "      <td>male</td>\n",
       "      <td>28.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>PC 17604</td>\n",
       "      <td>82.1708</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>64</td>\n",
       "      <td>0</td>\n",
       "      <td>63</td>\n",
       "      <td>3</td>\n",
       "      <td>Skoog, Master. Harald</td>\n",
       "      <td>male</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>347088</td>\n",
       "      <td>27.9000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>80</td>\n",
       "      <td>1</td>\n",
       "      <td>79</td>\n",
       "      <td>3</td>\n",
       "      <td>Dowdell, Miss. Elizabeth</td>\n",
       "      <td>female</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>364516</td>\n",
       "      <td>12.4750</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>84</td>\n",
       "      <td>0</td>\n",
       "      <td>83</td>\n",
       "      <td>1</td>\n",
       "      <td>Carrau, Mr. Francisco M</td>\n",
       "      <td>male</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>113059</td>\n",
       "      <td>47.1000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>101</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "      <td>3</td>\n",
       "      <td>Petranec, Miss. Matilda</td>\n",
       "      <td>female</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>349245</td>\n",
       "      <td>7.8958</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>106</td>\n",
       "      <td>0</td>\n",
       "      <td>105</td>\n",
       "      <td>3</td>\n",
       "      <td>Mionoff, Mr. Stoytcho</td>\n",
       "      <td>male</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>349207</td>\n",
       "      <td>7.8958</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>158</td>\n",
       "      <td>0</td>\n",
       "      <td>157</td>\n",
       "      <td>3</td>\n",
       "      <td>Corn, Mr. Harry</td>\n",
       "      <td>male</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>SOTON/OQ 392090</td>\n",
       "      <td>8.0500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>170</td>\n",
       "      <td>0</td>\n",
       "      <td>169</td>\n",
       "      <td>3</td>\n",
       "      <td>Ling, Mr. Lee</td>\n",
       "      <td>male</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1601</td>\n",
       "      <td>56.4958</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>172</td>\n",
       "      <td>0</td>\n",
       "      <td>171</td>\n",
       "      <td>3</td>\n",
       "      <td>Rice, Master. Arthur</td>\n",
       "      <td>male</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>382652</td>\n",
       "      <td>29.1250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Q</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178</th>\n",
       "      <td>179</td>\n",
       "      <td>0</td>\n",
       "      <td>178</td>\n",
       "      <td>2</td>\n",
       "      <td>Hale, Mr. Reginald</td>\n",
       "      <td>male</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>250653</td>\n",
       "      <td>13.0000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>185</td>\n",
       "      <td>1</td>\n",
       "      <td>184</td>\n",
       "      <td>3</td>\n",
       "      <td>Kink-Heilmann, Miss. Luise Gretchen</td>\n",
       "      <td>female</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>315153</td>\n",
       "      <td>22.0250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200</th>\n",
       "      <td>201</td>\n",
       "      <td>0</td>\n",
       "      <td>200</td>\n",
       "      <td>3</td>\n",
       "      <td>Vande Walle, Mr. Nestor Cyriel</td>\n",
       "      <td>male</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>345770</td>\n",
       "      <td>9.5000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>213</th>\n",
       "      <td>214</td>\n",
       "      <td>0</td>\n",
       "      <td>213</td>\n",
       "      <td>2</td>\n",
       "      <td>Givard, Mr. Hans Kristensen</td>\n",
       "      <td>male</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>250646</td>\n",
       "      <td>13.0000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219</th>\n",
       "      <td>220</td>\n",
       "      <td>0</td>\n",
       "      <td>219</td>\n",
       "      <td>2</td>\n",
       "      <td>Harris, Mr. Walter</td>\n",
       "      <td>male</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>W/C 14208</td>\n",
       "      <td>10.5000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>244</th>\n",
       "      <td>245</td>\n",
       "      <td>0</td>\n",
       "      <td>244</td>\n",
       "      <td>3</td>\n",
       "      <td>Attalah, Mr. Sleiman</td>\n",
       "      <td>male</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2694</td>\n",
       "      <td>7.2250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253</th>\n",
       "      <td>254</td>\n",
       "      <td>0</td>\n",
       "      <td>253</td>\n",
       "      <td>3</td>\n",
       "      <td>Lobb, Mr. William Arthur</td>\n",
       "      <td>male</td>\n",
       "      <td>30.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>A/5. 3336</td>\n",
       "      <td>16.1000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>257</th>\n",
       "      <td>258</td>\n",
       "      <td>1</td>\n",
       "      <td>257</td>\n",
       "      <td>1</td>\n",
       "      <td>Cherry, Miss. Gladys</td>\n",
       "      <td>female</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>110152</td>\n",
       "      <td>86.5000</td>\n",
       "      <td>B77</td>\n",
       "      <td>S</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>281</th>\n",
       "      <td>282</td>\n",
       "      <td>0</td>\n",
       "      <td>281</td>\n",
       "      <td>3</td>\n",
       "      <td>Olsson, Mr. Nils Johan Goransson</td>\n",
       "      <td>male</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>347464</td>\n",
       "      <td>7.8542</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>286</th>\n",
       "      <td>287</td>\n",
       "      <td>1</td>\n",
       "      <td>286</td>\n",
       "      <td>3</td>\n",
       "      <td>de Mulder, Mr. Theodore</td>\n",
       "      <td>male</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>345774</td>\n",
       "      <td>9.5000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>308</th>\n",
       "      <td>309</td>\n",
       "      <td>0</td>\n",
       "      <td>308</td>\n",
       "      <td>2</td>\n",
       "      <td>Abelson, Mr. Samuel</td>\n",
       "      <td>male</td>\n",
       "      <td>30.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>P/PP 3381</td>\n",
       "      <td>24.0000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>309</th>\n",
       "      <td>310</td>\n",
       "      <td>1</td>\n",
       "      <td>309</td>\n",
       "      <td>1</td>\n",
       "      <td>Francatelli, Miss. Laura Mabel</td>\n",
       "      <td>female</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>PC 17485</td>\n",
       "      <td>56.9292</td>\n",
       "      <td>E36</td>\n",
       "      <td>C</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>313</th>\n",
       "      <td>314</td>\n",
       "      <td>0</td>\n",
       "      <td>313</td>\n",
       "      <td>3</td>\n",
       "      <td>Hendekovic, Mr. Ignjac</td>\n",
       "      <td>male</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>349243</td>\n",
       "      <td>7.8958</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>322</th>\n",
       "      <td>323</td>\n",
       "      <td>1</td>\n",
       "      <td>322</td>\n",
       "      <td>2</td>\n",
       "      <td>Slayter, Miss. Hilda Mary</td>\n",
       "      <td>female</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>234818</td>\n",
       "      <td>12.3500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Q</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>342</th>\n",
       "      <td>343</td>\n",
       "      <td>0</td>\n",
       "      <td>342</td>\n",
       "      <td>2</td>\n",
       "      <td>Collander, Mr. Erik Gustaf</td>\n",
       "      <td>male</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>248740</td>\n",
       "      <td>13.0000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>355</th>\n",
       "      <td>356</td>\n",
       "      <td>0</td>\n",
       "      <td>355</td>\n",
       "      <td>3</td>\n",
       "      <td>Vanden Steen, Mr. Leo Peter</td>\n",
       "      <td>male</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>345783</td>\n",
       "      <td>9.5000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>365</th>\n",
       "      <td>366</td>\n",
       "      <td>0</td>\n",
       "      <td>365</td>\n",
       "      <td>3</td>\n",
       "      <td>Adahl, Mr. Mauritz Nils Martin</td>\n",
       "      <td>male</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>C 7076</td>\n",
       "      <td>7.2500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>366</th>\n",
       "      <td>367</td>\n",
       "      <td>1</td>\n",
       "      <td>366</td>\n",
       "      <td>1</td>\n",
       "      <td>Warren, Mrs. Frank Manley (Anna Sophia Atkinson)</td>\n",
       "      <td>female</td>\n",
       "      <td>60.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>110813</td>\n",
       "      <td>75.2500</td>\n",
       "      <td>D37</td>\n",
       "      <td>C</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>392</th>\n",
       "      <td>393</td>\n",
       "      <td>0</td>\n",
       "      <td>392</td>\n",
       "      <td>3</td>\n",
       "      <td>Gustafsson, Mr. Johan Birger</td>\n",
       "      <td>male</td>\n",
       "      <td>28.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>3101277</td>\n",
       "      <td>7.9250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>399</th>\n",
       "      <td>400</td>\n",
       "      <td>1</td>\n",
       "      <td>399</td>\n",
       "      <td>2</td>\n",
       "      <td>Trout, Mrs. William H (Jessie L)</td>\n",
       "      <td>female</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>240929</td>\n",
       "      <td>12.6500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>403</th>\n",
       "      <td>404</td>\n",
       "      <td>0</td>\n",
       "      <td>403</td>\n",
       "      <td>3</td>\n",
       "      <td>Hakkarainen, Mr. Pekka Pietari</td>\n",
       "      <td>male</td>\n",
       "      <td>28.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>STON/O2. 3101279</td>\n",
       "      <td>15.8500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>418</th>\n",
       "      <td>419</td>\n",
       "      <td>0</td>\n",
       "      <td>418</td>\n",
       "      <td>2</td>\n",
       "      <td>Matthews, Mr. William John</td>\n",
       "      <td>male</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>28228</td>\n",
       "      <td>13.0000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>423</th>\n",
       "      <td>424</td>\n",
       "      <td>0</td>\n",
       "      <td>423</td>\n",
       "      <td>3</td>\n",
       "      <td>Danbom, Mrs. Ernst Gilbert (Anna Sigrid Maria ...</td>\n",
       "      <td>female</td>\n",
       "      <td>28.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>347080</td>\n",
       "      <td>14.4000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>426</th>\n",
       "      <td>427</td>\n",
       "      <td>1</td>\n",
       "      <td>426</td>\n",
       "      <td>2</td>\n",
       "      <td>Clarke, Mrs. Charles V (Ada Maria Winfield)</td>\n",
       "      <td>female</td>\n",
       "      <td>28.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2003</td>\n",
       "      <td>26.0000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>430</th>\n",
       "      <td>431</td>\n",
       "      <td>1</td>\n",
       "      <td>430</td>\n",
       "      <td>1</td>\n",
       "      <td>Bjornstrom-Steffansson, Mr. Mauritz Hakan</td>\n",
       "      <td>male</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>110564</td>\n",
       "      <td>26.5500</td>\n",
       "      <td>C52</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>443</th>\n",
       "      <td>444</td>\n",
       "      <td>1</td>\n",
       "      <td>443</td>\n",
       "      <td>2</td>\n",
       "      <td>Reynaldo, Ms. Encarnacion</td>\n",
       "      <td>female</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>230434</td>\n",
       "      <td>13.0000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>445</th>\n",
       "      <td>446</td>\n",
       "      <td>1</td>\n",
       "      <td>445</td>\n",
       "      <td>1</td>\n",
       "      <td>Dodge, Master. Washington</td>\n",
       "      <td>male</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>33638</td>\n",
       "      <td>81.8583</td>\n",
       "      <td>A34</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>452</th>\n",
       "      <td>453</td>\n",
       "      <td>0</td>\n",
       "      <td>452</td>\n",
       "      <td>1</td>\n",
       "      <td>Foreman, Mr. Benjamin Laventall</td>\n",
       "      <td>male</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>113051</td>\n",
       "      <td>27.7500</td>\n",
       "      <td>C111</td>\n",
       "      <td>C</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>488</th>\n",
       "      <td>489</td>\n",
       "      <td>0</td>\n",
       "      <td>488</td>\n",
       "      <td>3</td>\n",
       "      <td>Somerton, Mr. Francis William</td>\n",
       "      <td>male</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>A.5. 18509</td>\n",
       "      <td>8.0500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>508</th>\n",
       "      <td>509</td>\n",
       "      <td>0</td>\n",
       "      <td>508</td>\n",
       "      <td>3</td>\n",
       "      <td>Olsen, Mr. Henry Margido</td>\n",
       "      <td>male</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>C 4001</td>\n",
       "      <td>22.5250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>520</th>\n",
       "      <td>521</td>\n",
       "      <td>1</td>\n",
       "      <td>520</td>\n",
       "      <td>1</td>\n",
       "      <td>Perreault, Miss. Anne</td>\n",
       "      <td>female</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12749</td>\n",
       "      <td>93.5000</td>\n",
       "      <td>B73</td>\n",
       "      <td>S</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>534</th>\n",
       "      <td>535</td>\n",
       "      <td>0</td>\n",
       "      <td>534</td>\n",
       "      <td>3</td>\n",
       "      <td>Cacic, Miss. Marija</td>\n",
       "      <td>female</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>315084</td>\n",
       "      <td>8.6625</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>537</th>\n",
       "      <td>538</td>\n",
       "      <td>1</td>\n",
       "      <td>537</td>\n",
       "      <td>1</td>\n",
       "      <td>LeRoy, Miss. Bertha</td>\n",
       "      <td>female</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>PC 17761</td>\n",
       "      <td>106.4250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>562</th>\n",
       "      <td>563</td>\n",
       "      <td>0</td>\n",
       "      <td>562</td>\n",
       "      <td>2</td>\n",
       "      <td>Norman, Mr. Robert Douglas</td>\n",
       "      <td>male</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>218629</td>\n",
       "      <td>13.5000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>587</th>\n",
       "      <td>588</td>\n",
       "      <td>1</td>\n",
       "      <td>587</td>\n",
       "      <td>1</td>\n",
       "      <td>Frolicher-Stehli, Mr. Maxmillian</td>\n",
       "      <td>male</td>\n",
       "      <td>60.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>13567</td>\n",
       "      <td>79.2000</td>\n",
       "      <td>B41</td>\n",
       "      <td>C</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>606</th>\n",
       "      <td>607</td>\n",
       "      <td>0</td>\n",
       "      <td>606</td>\n",
       "      <td>3</td>\n",
       "      <td>Karaic, Mr. Milan</td>\n",
       "      <td>male</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>349246</td>\n",
       "      <td>7.8958</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>618</th>\n",
       "      <td>619</td>\n",
       "      <td>1</td>\n",
       "      <td>618</td>\n",
       "      <td>2</td>\n",
       "      <td>Becker, Miss. Marion Louise</td>\n",
       "      <td>female</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>230136</td>\n",
       "      <td>39.0000</td>\n",
       "      <td>F4</td>\n",
       "      <td>S</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>635</th>\n",
       "      <td>636</td>\n",
       "      <td>1</td>\n",
       "      <td>635</td>\n",
       "      <td>2</td>\n",
       "      <td>Davis, Miss. Mary</td>\n",
       "      <td>female</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>237668</td>\n",
       "      <td>13.0000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>684</th>\n",
       "      <td>685</td>\n",
       "      <td>0</td>\n",
       "      <td>684</td>\n",
       "      <td>2</td>\n",
       "      <td>Brown, Mr. Thomas William Solomon</td>\n",
       "      <td>male</td>\n",
       "      <td>60.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>29750</td>\n",
       "      <td>39.0000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>691</th>\n",
       "      <td>692</td>\n",
       "      <td>1</td>\n",
       "      <td>691</td>\n",
       "      <td>3</td>\n",
       "      <td>Karun, Miss. Manca</td>\n",
       "      <td>female</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>349256</td>\n",
       "      <td>13.4167</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>694</th>\n",
       "      <td>695</td>\n",
       "      <td>0</td>\n",
       "      <td>694</td>\n",
       "      <td>1</td>\n",
       "      <td>Weir, Col. John</td>\n",
       "      <td>male</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>113800</td>\n",
       "      <td>26.5500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>726</th>\n",
       "      <td>727</td>\n",
       "      <td>1</td>\n",
       "      <td>726</td>\n",
       "      <td>2</td>\n",
       "      <td>Renouf, Mrs. Peter Henry (Lillian Jefferys)</td>\n",
       "      <td>female</td>\n",
       "      <td>30.0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>31027</td>\n",
       "      <td>21.0000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>747</th>\n",
       "      <td>748</td>\n",
       "      <td>1</td>\n",
       "      <td>747</td>\n",
       "      <td>2</td>\n",
       "      <td>Sinkkonen, Miss. Anna</td>\n",
       "      <td>female</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>250648</td>\n",
       "      <td>13.0000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>750</th>\n",
       "      <td>751</td>\n",
       "      <td>1</td>\n",
       "      <td>750</td>\n",
       "      <td>2</td>\n",
       "      <td>Wells, Miss. Joan</td>\n",
       "      <td>female</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>29103</td>\n",
       "      <td>23.0000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>756</th>\n",
       "      <td>757</td>\n",
       "      <td>0</td>\n",
       "      <td>756</td>\n",
       "      <td>3</td>\n",
       "      <td>Carlsson, Mr. August Sigfrid</td>\n",
       "      <td>male</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>350042</td>\n",
       "      <td>7.7958</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>798</th>\n",
       "      <td>799</td>\n",
       "      <td>0</td>\n",
       "      <td>798</td>\n",
       "      <td>3</td>\n",
       "      <td>Ibrahim Shawah, Mr. Yousseff</td>\n",
       "      <td>male</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2685</td>\n",
       "      <td>7.2292</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>799</th>\n",
       "      <td>800</td>\n",
       "      <td>0</td>\n",
       "      <td>799</td>\n",
       "      <td>3</td>\n",
       "      <td>Van Impe, Mrs. Jean Baptiste (Rosalie Paula Go...</td>\n",
       "      <td>female</td>\n",
       "      <td>30.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>345773</td>\n",
       "      <td>24.1500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>842</th>\n",
       "      <td>843</td>\n",
       "      <td>1</td>\n",
       "      <td>842</td>\n",
       "      <td>1</td>\n",
       "      <td>Serepeca, Miss. Augusta</td>\n",
       "      <td>female</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>113798</td>\n",
       "      <td>31.0000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>848</th>\n",
       "      <td>849</td>\n",
       "      <td>0</td>\n",
       "      <td>848</td>\n",
       "      <td>2</td>\n",
       "      <td>Harper, Rev. John</td>\n",
       "      <td>male</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>248727</td>\n",
       "      <td>33.0000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>850</th>\n",
       "      <td>851</td>\n",
       "      <td>0</td>\n",
       "      <td>850</td>\n",
       "      <td>3</td>\n",
       "      <td>Andersson, Master. Sigvard Harald Elias</td>\n",
       "      <td>male</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>347082</td>\n",
       "      <td>31.2750</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>869</th>\n",
       "      <td>870</td>\n",
       "      <td>1</td>\n",
       "      <td>869</td>\n",
       "      <td>3</td>\n",
       "      <td>Johnson, Master. Harold Theodor</td>\n",
       "      <td>male</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>347742</td>\n",
       "      <td>11.1333</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>874</th>\n",
       "      <td>875</td>\n",
       "      <td>1</td>\n",
       "      <td>874</td>\n",
       "      <td>2</td>\n",
       "      <td>Abelson, Mrs. Samuel (Hannah Wizosky)</td>\n",
       "      <td>female</td>\n",
       "      <td>28.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>P/PP 3381</td>\n",
       "      <td>24.0000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C</td>\n",
       "      <td>女性</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>883</th>\n",
       "      <td>884</td>\n",
       "      <td>0</td>\n",
       "      <td>883</td>\n",
       "      <td>2</td>\n",
       "      <td>Banfield, Mr. Frederick James</td>\n",
       "      <td>male</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>C.A./SOTON 34068</td>\n",
       "      <td>10.5000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>S</td>\n",
       "      <td>男性</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    PassengerId  Survived  new_variable3  Pclass  \\\n",
       "10           11         1             10       3   \n",
       "23           24         1             23       1   \n",
       "34           35         0             34       1   \n",
       "63           64         0             63       3   \n",
       "79           80         1             79       3   \n",
       "83           84         0             83       1   \n",
       "100         101         0            100       3   \n",
       "105         106         0            105       3   \n",
       "157         158         0            157       3   \n",
       "169         170         0            169       3   \n",
       "171         172         0            171       3   \n",
       "178         179         0            178       2   \n",
       "184         185         1            184       3   \n",
       "200         201         0            200       3   \n",
       "213         214         0            213       2   \n",
       "219         220         0            219       2   \n",
       "244         245         0            244       3   \n",
       "253         254         0            253       3   \n",
       "257         258         1            257       1   \n",
       "281         282         0            281       3   \n",
       "286         287         1            286       3   \n",
       "308         309         0            308       2   \n",
       "309         310         1            309       1   \n",
       "313         314         0            313       3   \n",
       "322         323         1            322       2   \n",
       "342         343         0            342       2   \n",
       "355         356         0            355       3   \n",
       "365         366         0            365       3   \n",
       "366         367         1            366       1   \n",
       "392         393         0            392       3   \n",
       "399         400         1            399       2   \n",
       "403         404         0            403       3   \n",
       "418         419         0            418       2   \n",
       "423         424         0            423       3   \n",
       "426         427         1            426       2   \n",
       "430         431         1            430       1   \n",
       "443         444         1            443       2   \n",
       "445         446         1            445       1   \n",
       "452         453         0            452       1   \n",
       "488         489         0            488       3   \n",
       "508         509         0            508       3   \n",
       "520         521         1            520       1   \n",
       "534         535         0            534       3   \n",
       "537         538         1            537       1   \n",
       "562         563         0            562       2   \n",
       "587         588         1            587       1   \n",
       "606         607         0            606       3   \n",
       "618         619         1            618       2   \n",
       "635         636         1            635       2   \n",
       "684         685         0            684       2   \n",
       "691         692         1            691       3   \n",
       "694         695         0            694       1   \n",
       "726         727         1            726       2   \n",
       "747         748         1            747       2   \n",
       "750         751         1            750       2   \n",
       "756         757         0            756       3   \n",
       "798         799         0            798       3   \n",
       "799         800         0            799       3   \n",
       "842         843         1            842       1   \n",
       "848         849         0            848       2   \n",
       "850         851         0            850       3   \n",
       "869         870         1            869       3   \n",
       "874         875         1            874       2   \n",
       "883         884         0            883       2   \n",
       "\n",
       "                                                  Name     Sex   Age  SibSp  \\\n",
       "10                     Sandstrom, Miss. Marguerite Rut  female   4.0      1   \n",
       "23                        Sloper, Mr. William Thompson    male  28.0      0   \n",
       "34                             Meyer, Mr. Edgar Joseph    male  28.0      1   \n",
       "63                               Skoog, Master. Harald    male   4.0      3   \n",
       "79                            Dowdell, Miss. Elizabeth  female  30.0      0   \n",
       "83                             Carrau, Mr. Francisco M    male  28.0      0   \n",
       "100                            Petranec, Miss. Matilda  female  28.0      0   \n",
       "105                              Mionoff, Mr. Stoytcho    male  28.0      0   \n",
       "157                                    Corn, Mr. Harry    male  30.0      0   \n",
       "169                                      Ling, Mr. Lee    male  28.0      0   \n",
       "171                               Rice, Master. Arthur    male   4.0      4   \n",
       "178                                 Hale, Mr. Reginald    male  30.0      0   \n",
       "184                Kink-Heilmann, Miss. Luise Gretchen  female   4.0      0   \n",
       "200                     Vande Walle, Mr. Nestor Cyriel    male  28.0      0   \n",
       "213                        Givard, Mr. Hans Kristensen    male  30.0      0   \n",
       "219                                 Harris, Mr. Walter    male  30.0      0   \n",
       "244                               Attalah, Mr. Sleiman    male  30.0      0   \n",
       "253                           Lobb, Mr. William Arthur    male  30.0      1   \n",
       "257                               Cherry, Miss. Gladys  female  30.0      0   \n",
       "281                   Olsson, Mr. Nils Johan Goransson    male  28.0      0   \n",
       "286                            de Mulder, Mr. Theodore    male  30.0      0   \n",
       "308                                Abelson, Mr. Samuel    male  30.0      1   \n",
       "309                     Francatelli, Miss. Laura Mabel  female  30.0      0   \n",
       "313                             Hendekovic, Mr. Ignjac    male  28.0      0   \n",
       "322                          Slayter, Miss. Hilda Mary  female  30.0      0   \n",
       "342                         Collander, Mr. Erik Gustaf    male  28.0      0   \n",
       "355                        Vanden Steen, Mr. Leo Peter    male  28.0      0   \n",
       "365                     Adahl, Mr. Mauritz Nils Martin    male  30.0      0   \n",
       "366   Warren, Mrs. Frank Manley (Anna Sophia Atkinson)  female  60.0      1   \n",
       "392                       Gustafsson, Mr. Johan Birger    male  28.0      2   \n",
       "399                   Trout, Mrs. William H (Jessie L)  female  28.0      0   \n",
       "403                     Hakkarainen, Mr. Pekka Pietari    male  28.0      1   \n",
       "418                         Matthews, Mr. William John    male  30.0      0   \n",
       "423  Danbom, Mrs. Ernst Gilbert (Anna Sigrid Maria ...  female  28.0      1   \n",
       "426        Clarke, Mrs. Charles V (Ada Maria Winfield)  female  28.0      1   \n",
       "430          Bjornstrom-Steffansson, Mr. Mauritz Hakan    male  28.0      0   \n",
       "443                          Reynaldo, Ms. Encarnacion  female  28.0      0   \n",
       "445                          Dodge, Master. Washington    male   4.0      0   \n",
       "452                    Foreman, Mr. Benjamin Laventall    male  30.0      0   \n",
       "488                      Somerton, Mr. Francis William    male  30.0      0   \n",
       "508                           Olsen, Mr. Henry Margido    male  28.0      0   \n",
       "520                              Perreault, Miss. Anne  female  30.0      0   \n",
       "534                                Cacic, Miss. Marija  female  30.0      0   \n",
       "537                                LeRoy, Miss. Bertha  female  30.0      0   \n",
       "562                         Norman, Mr. Robert Douglas    male  28.0      0   \n",
       "587                   Frolicher-Stehli, Mr. Maxmillian    male  60.0      1   \n",
       "606                                  Karaic, Mr. Milan    male  30.0      0   \n",
       "618                        Becker, Miss. Marion Louise  female   4.0      2   \n",
       "635                                  Davis, Miss. Mary  female  28.0      0   \n",
       "684                  Brown, Mr. Thomas William Solomon    male  60.0      1   \n",
       "691                                 Karun, Miss. Manca  female   4.0      0   \n",
       "694                                    Weir, Col. John    male  60.0      0   \n",
       "726        Renouf, Mrs. Peter Henry (Lillian Jefferys)  female  30.0      3   \n",
       "747                              Sinkkonen, Miss. Anna  female  30.0      0   \n",
       "750                                  Wells, Miss. Joan  female   4.0      1   \n",
       "756                       Carlsson, Mr. August Sigfrid    male  28.0      0   \n",
       "798                       Ibrahim Shawah, Mr. Yousseff    male  30.0      0   \n",
       "799  Van Impe, Mrs. Jean Baptiste (Rosalie Paula Go...  female  30.0      1   \n",
       "842                            Serepeca, Miss. Augusta  female  30.0      0   \n",
       "848                                  Harper, Rev. John    male  28.0      0   \n",
       "850            Andersson, Master. Sigvard Harald Elias    male   4.0      4   \n",
       "869                    Johnson, Master. Harold Theodor    male   4.0      1   \n",
       "874              Abelson, Mrs. Samuel (Hannah Wizosky)  female  28.0      1   \n",
       "883                      Banfield, Mr. Frederick James    male  28.0      0   \n",
       "\n",
       "     Parch            Ticket      Fare Cabin Embarked sex2  \n",
       "10       1           PP 9549   16.7000    G6        S   女性  \n",
       "23       0            113788   35.5000    A6        S   男性  \n",
       "34       0          PC 17604   82.1708   NaN        C   男性  \n",
       "63       2            347088   27.9000   NaN        S   男性  \n",
       "79       0            364516   12.4750   NaN        S   女性  \n",
       "83       0            113059   47.1000   NaN        S   男性  \n",
       "100      0            349245    7.8958   NaN        S   女性  \n",
       "105      0            349207    7.8958   NaN        S   男性  \n",
       "157      0   SOTON/OQ 392090    8.0500   NaN        S   男性  \n",
       "169      0              1601   56.4958   NaN        S   男性  \n",
       "171      1            382652   29.1250   NaN        Q   男性  \n",
       "178      0            250653   13.0000   NaN        S   男性  \n",
       "184      2            315153   22.0250   NaN        S   女性  \n",
       "200      0            345770    9.5000   NaN        S   男性  \n",
       "213      0            250646   13.0000   NaN        S   男性  \n",
       "219      0         W/C 14208   10.5000   NaN        S   男性  \n",
       "244      0              2694    7.2250   NaN        C   男性  \n",
       "253      0         A/5. 3336   16.1000   NaN        S   男性  \n",
       "257      0            110152   86.5000   B77        S   女性  \n",
       "281      0            347464    7.8542   NaN        S   男性  \n",
       "286      0            345774    9.5000   NaN        S   男性  \n",
       "308      0         P/PP 3381   24.0000   NaN        C   男性  \n",
       "309      0          PC 17485   56.9292   E36        C   女性  \n",
       "313      0            349243    7.8958   NaN        S   男性  \n",
       "322      0            234818   12.3500   NaN        Q   女性  \n",
       "342      0            248740   13.0000   NaN        S   男性  \n",
       "355      0            345783    9.5000   NaN        S   男性  \n",
       "365      0            C 7076    7.2500   NaN        S   男性  \n",
       "366      0            110813   75.2500   D37        C   女性  \n",
       "392      0           3101277    7.9250   NaN        S   男性  \n",
       "399      0            240929   12.6500   NaN        S   女性  \n",
       "403      0  STON/O2. 3101279   15.8500   NaN        S   男性  \n",
       "418      0             28228   13.0000   NaN        S   男性  \n",
       "423      1            347080   14.4000   NaN        S   女性  \n",
       "426      0              2003   26.0000   NaN        S   女性  \n",
       "430      0            110564   26.5500   C52        S   男性  \n",
       "443      0            230434   13.0000   NaN        S   女性  \n",
       "445      2             33638   81.8583   A34        S   男性  \n",
       "452      0            113051   27.7500  C111        C   男性  \n",
       "488      0        A.5. 18509    8.0500   NaN        S   男性  \n",
       "508      0            C 4001   22.5250   NaN        S   男性  \n",
       "520      0             12749   93.5000   B73        S   女性  \n",
       "534      0            315084    8.6625   NaN        S   女性  \n",
       "537      0          PC 17761  106.4250   NaN        C   女性  \n",
       "562      0            218629   13.5000   NaN        S   男性  \n",
       "587      1             13567   79.2000   B41        C   男性  \n",
       "606      0            349246    7.8958   NaN        S   男性  \n",
       "618      1            230136   39.0000    F4        S   女性  \n",
       "635      0            237668   13.0000   NaN        S   女性  \n",
       "684      1             29750   39.0000   NaN        S   男性  \n",
       "691      1            349256   13.4167   NaN        C   女性  \n",
       "694      0            113800   26.5500   NaN        S   男性  \n",
       "726      0             31027   21.0000   NaN        S   女性  \n",
       "747      0            250648   13.0000   NaN        S   女性  \n",
       "750      1             29103   23.0000   NaN        S   女性  \n",
       "756      0            350042    7.7958   NaN        S   男性  \n",
       "798      0              2685    7.2292   NaN        C   男性  \n",
       "799      1            345773   24.1500   NaN        S   女性  \n",
       "842      0            113798   31.0000   NaN        C   女性  \n",
       "848      1            248727   33.0000   NaN        S   男性  \n",
       "850      2            347082   31.2750   NaN        S   男性  \n",
       "869      1            347742   11.1333   NaN        S   男性  \n",
       "874      0         P/PP 3381   24.0000   NaN        C   女性  \n",
       "883      0  C.A./SOTON 34068   10.5000   NaN        S   男性  "
      ]
     },
     "execution_count": 339,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# pd.isin()\n",
    "# 包含\n",
    "df2[df2['Age'].isin([4,28,30,60])]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b85701ff",
   "metadata": {},
   "source": [
    "#### 字符串向量化操作 str\n",
    "\n",
    "- 见后面内容"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "758dbb41",
   "metadata": {},
   "source": [
    "## 数据合并\n",
    "\n",
    "- 有join, merge 和 concat 三种方法，用于合并，支持 左链接，右链接，内链接，外链接"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1bf8d61",
   "metadata": {},
   "source": [
    "### Join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 351,
   "id": "a7468a29",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Red  Green\n",
      "a    1      5\n",
      "b    3      0\n",
      "c    5      3\n",
      "   Blue  Yellow\n",
      "c     1       6\n",
      "d     9       6\n",
      "e     8       7\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Red</th>\n",
       "      <th>Green</th>\n",
       "      <th>Blue</th>\n",
       "      <th>Yellow</th>\n",
       "      <th>Brown</th>\n",
       "      <th>White</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Red  Green  Blue  Yellow  Brown  White\n",
       "a  1.0    5.0   NaN     NaN    3.0    1.0\n",
       "b  3.0    0.0   NaN     NaN    NaN    NaN\n",
       "c  5.0    3.0   1.0     6.0    NaN    NaN"
      ]
     },
     "execution_count": 351,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3=pd.DataFrame({'Red':[1,3,5],'Green':[5,0,3]},index=list('abc'))\n",
    "df4=pd.DataFrame({'Blue':[1,9,8],'Yellow':[6,6,7]},index=list('cde'))\n",
    "print(df3)\n",
    "print(df4)\n",
    "\n",
    "# 默认左连接\n",
    "df3.join(df4)  #,how = 'left'\n",
    "\n",
    "# 右连接\n",
    "df3.join(df4,how='right')\n",
    "\n",
    "# 内连接\n",
    "df3.join(df4,how='inner')\n",
    "\n",
    "# 外连接\n",
    "df3.join(df4,how = 'outer')\n",
    "\n",
    "df5=pd.DataFrame({'Brown':[3,4,5],'White':[1,1,2]},index=list('aed'))\n",
    "df3.join([df4,df5])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3d345b54",
   "metadata": {},
   "source": [
    "### Merge\n",
    "\n",
    "- 使用merge，着重关注的是列的合并\n",
    "- 合并使用关联字段必须类型一致"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 353,
   "id": "edcf7d44",
   "metadata": {},
   "outputs": [],
   "source": [
    "baby_trade = pd.read_csv('baby_trade_history.csv', encoding='utf-8',dtype={'user_id':str})# 交易数据\n",
    "baby_info = pd.read_csv('sam_tianchi_mum_baby.csv',encoding = 'utf-8',dtype =str)#婴儿信息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 354,
   "id": "f2e7f4f0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>auction_id</th>\n",
       "      <th>cat_id</th>\n",
       "      <th>cat1</th>\n",
       "      <th>property</th>\n",
       "      <th>buy_mount</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>786295544</td>\n",
       "      <td>41098319944</td>\n",
       "      <td>50014866</td>\n",
       "      <td>50022520</td>\n",
       "      <td>21458:86755362;13023209:3593274;10984217:21985...</td>\n",
       "      <td>2</td>\n",
       "      <td>20140919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>532110457</td>\n",
       "      <td>17916191097</td>\n",
       "      <td>50011993</td>\n",
       "      <td>28</td>\n",
       "      <td>21458:11399317;1628862:3251296;21475:137325;16...</td>\n",
       "      <td>1</td>\n",
       "      <td>20131011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>249013725</td>\n",
       "      <td>21896936223</td>\n",
       "      <td>50012461</td>\n",
       "      <td>50014815</td>\n",
       "      <td>21458:30992;1628665:92012;1628665:3233938;1628...</td>\n",
       "      <td>1</td>\n",
       "      <td>20131011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>917056007</td>\n",
       "      <td>12515996043</td>\n",
       "      <td>50018831</td>\n",
       "      <td>50014815</td>\n",
       "      <td>21458:15841995;21956:3494076;27000458:59723383...</td>\n",
       "      <td>2</td>\n",
       "      <td>20141023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>444069173</td>\n",
       "      <td>20487688075</td>\n",
       "      <td>50013636</td>\n",
       "      <td>50008168</td>\n",
       "      <td>21458:30992;13658074:3323064;1628665:3233941;1...</td>\n",
       "      <td>1</td>\n",
       "      <td>20141103</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     user_id   auction_id    cat_id      cat1  \\\n",
       "0  786295544  41098319944  50014866  50022520   \n",
       "1  532110457  17916191097  50011993        28   \n",
       "2  249013725  21896936223  50012461  50014815   \n",
       "3  917056007  12515996043  50018831  50014815   \n",
       "4  444069173  20487688075  50013636  50008168   \n",
       "\n",
       "                                            property  buy_mount       day  \n",
       "0  21458:86755362;13023209:3593274;10984217:21985...          2  20140919  \n",
       "1  21458:11399317;1628862:3251296;21475:137325;16...          1  20131011  \n",
       "2  21458:30992;1628665:92012;1628665:3233938;1628...          1  20131011  \n",
       "3  21458:15841995;21956:3494076;27000458:59723383...          2  20141023  \n",
       "4  21458:30992;13658074:3323064;1628665:3233941;1...          1  20141103  "
      ]
     },
     "execution_count": 354,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "baby_trade.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 355,
   "id": "4bfa213a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>birthday</th>\n",
       "      <th>gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2757</td>\n",
       "      <td>20130311</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>415971</td>\n",
       "      <td>20121111</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1372572</td>\n",
       "      <td>20120130</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10339332</td>\n",
       "      <td>20110910</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10642245</td>\n",
       "      <td>20130213</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    user_id  birthday gender\n",
       "0      2757  20130311      1\n",
       "1    415971  20121111      0\n",
       "2   1372572  20120130      1\n",
       "3  10339332  20110910      0\n",
       "4  10642245  20130213      0"
      ]
     },
     "execution_count": 355,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "baby_info.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 371,
   "id": "840d8c7d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>birthday</th>\n",
       "      <th>gender</th>\n",
       "      <th>day_left</th>\n",
       "      <th>auction_id</th>\n",
       "      <th>cat_id</th>\n",
       "      <th>cat1</th>\n",
       "      <th>property</th>\n",
       "      <th>buy_mount</th>\n",
       "      <th>day_right</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2757</td>\n",
       "      <td>20130311</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17429550751</td>\n",
       "      <td>50010555</td>\n",
       "      <td>50008168</td>\n",
       "      <td>21458:30992;25935:31381;1628665:29796;1628665:...</td>\n",
       "      <td>1</td>\n",
       "      <td>20130410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>415971</td>\n",
       "      <td>20121111</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20854308837</td>\n",
       "      <td>50010548</td>\n",
       "      <td>50008168</td>\n",
       "      <td>1628665:131622;25935:21991;22019:31001;22019:3...</td>\n",
       "      <td>1</td>\n",
       "      <td>20130128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1372572</td>\n",
       "      <td>20120130</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>16915013171</td>\n",
       "      <td>50008845</td>\n",
       "      <td>28</td>\n",
       "      <td>21458:30992;1628665:3233941;1628665:3233942;16...</td>\n",
       "      <td>1</td>\n",
       "      <td>20130327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10339332</td>\n",
       "      <td>20110910</td>\n",
       "      <td>0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>13174075495</td>\n",
       "      <td>50001732</td>\n",
       "      <td>50014815</td>\n",
       "      <td>21458:3409452;3066697:92335415;2815901:9233541...</td>\n",
       "      <td>1</td>\n",
       "      <td>20140526</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10642245</td>\n",
       "      <td>20130213</td>\n",
       "      <td>0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>14109039851</td>\n",
       "      <td>50006843</td>\n",
       "      <td>38</td>\n",
       "      <td>21458:7142737;8694098:95303334;12786373:54223;...</td>\n",
       "      <td>1</td>\n",
       "      <td>20130617</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    user_id  birthday gender  day_left   auction_id    cat_id      cat1  \\\n",
       "0      2757  20130311      1       0.0  17429550751  50010555  50008168   \n",
       "1    415971  20121111      0       1.0  20854308837  50010548  50008168   \n",
       "2   1372572  20120130      1       2.0  16915013171  50008845        28   \n",
       "3  10339332  20110910      0       3.0  13174075495  50001732  50014815   \n",
       "4  10642245  20130213      0       4.0  14109039851  50006843        38   \n",
       "\n",
       "                                            property  buy_mount  day_right  \n",
       "0  21458:30992;25935:31381;1628665:29796;1628665:...          1   20130410  \n",
       "1  1628665:131622;25935:21991;22019:31001;22019:3...          1   20130128  \n",
       "2  21458:30992;1628665:3233941;1628665:3233942;16...          1   20130327  \n",
       "3  21458:3409452;3066697:92335415;2815901:9233541...          1   20140526  \n",
       "4  21458:7142737;8694098:95303334;12786373:54223;...          1   20130617  "
      ]
     },
     "execution_count": 371,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge()\n",
    "baby_all = pd.merge(baby_info, baby_trade,on='user_id', how = 'outer')  \n",
    "baby_all.head()\n",
    "# how = 'right', 'left','outer', 默认是 'inner'\n",
    "# 如果两个表合并依据的变量名不一致，使用  right_on ='user_id', left_on = 'user_id'\n",
    "# 如果使用索引， right_index = True,  left_index = True\n",
    "# 若有相同列且该列没有作为合并的列，可通过suffixes设置该列的后缀名，一般为元组和列表类型： suffixes = ('_left', '_right') \n",
    "\n",
    "baby_info['day'] = range(baby_info.shape[0])\n",
    "baby_all = pd.merge(baby_info, baby_trade,on='user_id', how = 'outer',suffixes=('_left','_right'))  \n",
    "baby_all.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa28a0f9",
   "metadata": {},
   "source": [
    "###  concat\n",
    "\n",
    "- 轴向连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 383,
   "id": "25abde5d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a    1\n",
      "b    2\n",
      "dtype: int64\n",
      "b    3\n",
      "d    4\n",
      "e    5\n",
      "dtype: int64\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "a    1\n",
       "b    2\n",
       "b    3\n",
       "d    4\n",
       "e    5\n",
       "dtype: int64"
      ]
     },
     "execution_count": 383,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Series对象的连接\n",
    "s1=pd.Series([1,2],index=list('ab'))\n",
    "s2=pd.Series([3,4,5],index=list('bde'))\n",
    "print(s1)\n",
    "print(s2)\n",
    "pd.concat([s1,s2])  # 默认纵向堆叠\n",
    "\n",
    "# pd.concat([s1,s2],axis=1)  # 横向堆叠 \n",
    "\n",
    "# pd.concat([s1,s2],axis=1,join='inner')\n",
    "\n",
    "# pd.concat([s1,s2],axis=1,join='outer',keys=['A','B']) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 430,
   "id": "96f22dcc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Red  Green\n",
      "a    1      5\n",
      "b    3      0\n",
      "d    5      3\n",
      "   Blue  Yellow\n",
      "c     1       6\n",
      "e     9       6\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">A</th>\n",
       "      <th colspan=\"2\" halign=\"left\">B</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Red</th>\n",
       "      <th>Green</th>\n",
       "      <th>Blue</th>\n",
       "      <th>Yellow</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>e</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A          B       \n",
       "   Red Green Blue Yellow\n",
       "a  1.0   5.0  NaN    NaN\n",
       "b  3.0   0.0  NaN    NaN\n",
       "d  5.0   3.0  NaN    NaN\n",
       "c  NaN   NaN  1.0    6.0\n",
       "e  NaN   NaN  9.0    6.0"
      ]
     },
     "execution_count": 430,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# dataframe对象的连接\n",
    "\n",
    "df3=pd.DataFrame({'Red':[1,3,5],'Green':[5,0,3]},index=list('abd'))\n",
    "df4=pd.DataFrame({'Blue':[1,9],'Yellow':[6,6]},index=list('ce'))\n",
    "print(df3)\n",
    "print(df4) \n",
    "\n",
    "pd.concat([df3,df4]) \n",
    "\n",
    "pd.concat([df3,df4],axis=1)\n",
    "\n",
    "pd.concat({'A':df3,'B':df4},axis=1)\n",
    "# pd.concat([df3,df4],axis = 1, keys = ['A','B'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1446e2c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import xlrd\n",
    "# workbook = xlrd.open_workbook('meal_order_detail.xlsx')\n",
    "# sheet_name = workbook.sheet_names() #返回所有sheet的列表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "47eefd41",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3647, 19)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# example: 合并三个时期的数据，纵向堆叠，meal_order_detail 有三个表\n",
    "# 查看一下数据\n",
    "data1 = pd.read_excel('meal_order_detail.xlsx',sheet_name=0)\n",
    "data2 = pd.read_excel('meal_order_detail.xlsx',sheet_name=1)\n",
    "data2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 413,
   "id": "f64bc4c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_all = pd.DataFrame()\n",
    "for i in range(3):\n",
    "    tem = pd.read_excel('meal_order_detail.xlsx',sheet_name = i)\n",
    "    data_all = pd.concat([data_all,tem])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 416,
   "id": "947acb1f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>detail_id</th>\n",
       "      <th>order_id</th>\n",
       "      <th>dishes_id</th>\n",
       "      <th>logicprn_name</th>\n",
       "      <th>parent_class_name</th>\n",
       "      <th>dishes_name</th>\n",
       "      <th>itemis_add</th>\n",
       "      <th>counts</th>\n",
       "      <th>amounts</th>\n",
       "      <th>cost</th>\n",
       "      <th>place_order_time</th>\n",
       "      <th>discount_amt</th>\n",
       "      <th>discount_reason</th>\n",
       "      <th>kick_back</th>\n",
       "      <th>add_inprice</th>\n",
       "      <th>add_info</th>\n",
       "      <th>bar_code</th>\n",
       "      <th>picture_file</th>\n",
       "      <th>emp_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3606</th>\n",
       "      <td>5683</td>\n",
       "      <td>672</td>\n",
       "      <td>610049</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>爆炒双丝</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>35</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-08-31 21:53:30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>caipu/301003.jpg</td>\n",
       "      <td>1089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3607</th>\n",
       "      <td>5686</td>\n",
       "      <td>672</td>\n",
       "      <td>609959</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>小炒羊腰_x000D_\\n_x000D_\\n_x000D_\\n</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>36</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-08-31 21:54:40</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>caipu/202005.jpg</td>\n",
       "      <td>1089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3608</th>\n",
       "      <td>5379</td>\n",
       "      <td>647</td>\n",
       "      <td>610012</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>香菇鹌鹑蛋</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>39</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-08-31 21:54:44</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>caipu/302001.jpg</td>\n",
       "      <td>1094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3609</th>\n",
       "      <td>5380</td>\n",
       "      <td>647</td>\n",
       "      <td>610054</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>不加一滴油的酸奶蛋糕</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-08-31 21:55:24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>caipu/501003.jpg</td>\n",
       "      <td>1094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3610</th>\n",
       "      <td>5688</td>\n",
       "      <td>672</td>\n",
       "      <td>609953</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>凉拌菠菜</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-08-31 21:56:54</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>caipu/303004.jpg</td>\n",
       "      <td>1089</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      detail_id  order_id  dishes_id  logicprn_name  parent_class_name  \\\n",
       "3606       5683       672     610049            NaN                NaN   \n",
       "3607       5686       672     609959            NaN                NaN   \n",
       "3608       5379       647     610012            NaN                NaN   \n",
       "3609       5380       647     610054            NaN                NaN   \n",
       "3610       5688       672     609953            NaN                NaN   \n",
       "\n",
       "                          dishes_name  itemis_add  counts  amounts  cost  \\\n",
       "3606                             爆炒双丝           0       1       35   NaN   \n",
       "3607  小炒羊腰_x000D_\\n_x000D_\\n_x000D_\\n           0       1       36   NaN   \n",
       "3608                            香菇鹌鹑蛋           0       1       39   NaN   \n",
       "3609                       不加一滴油的酸奶蛋糕           0       1        7   NaN   \n",
       "3610                             凉拌菠菜           0       1       27   NaN   \n",
       "\n",
       "        place_order_time  discount_amt  discount_reason  kick_back  \\\n",
       "3606 2016-08-31 21:53:30           NaN              NaN        NaN   \n",
       "3607 2016-08-31 21:54:40           NaN              NaN        NaN   \n",
       "3608 2016-08-31 21:54:44           NaN              NaN        NaN   \n",
       "3609 2016-08-31 21:55:24           NaN              NaN        NaN   \n",
       "3610 2016-08-31 21:56:54           NaN              NaN        NaN   \n",
       "\n",
       "      add_inprice  add_info  bar_code      picture_file  emp_id  \n",
       "3606            0       NaN       NaN  caipu/301003.jpg    1089  \n",
       "3607            0       NaN       NaN  caipu/202005.jpg    1089  \n",
       "3608            0       NaN       NaN  caipu/302001.jpg    1094  \n",
       "3609            0       NaN       NaN  caipu/501003.jpg    1094  \n",
       "3610            0       NaN       NaN  caipu/303004.jpg    1089  "
      ]
     },
     "execution_count": 416,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_all.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89ac539e",
   "metadata": {},
   "source": [
    "## 层次化索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 431,
   "id": "9cdd2079",
   "metadata": {},
   "outputs": [],
   "source": [
    "baby_trade = pd.read_csv('baby_trade_history.csv', dtype={'user_id':str},index_col=[3,0]) #将数据第4列和第1列当成索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 432,
   "id": "e95af9d8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>auction_id</th>\n",
       "      <th>cat_id</th>\n",
       "      <th>property</th>\n",
       "      <th>buy_mount</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cat1</th>\n",
       "      <th>user_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>50022520</th>\n",
       "      <th>786295544</th>\n",
       "      <td>41098319944</td>\n",
       "      <td>50014866</td>\n",
       "      <td>21458:86755362;13023209:3593274;10984217:21985...</td>\n",
       "      <td>2</td>\n",
       "      <td>20140919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <th>532110457</th>\n",
       "      <td>17916191097</td>\n",
       "      <td>50011993</td>\n",
       "      <td>21458:11399317;1628862:3251296;21475:137325;16...</td>\n",
       "      <td>1</td>\n",
       "      <td>20131011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">50014815</th>\n",
       "      <th>249013725</th>\n",
       "      <td>21896936223</td>\n",
       "      <td>50012461</td>\n",
       "      <td>21458:30992;1628665:92012;1628665:3233938;1628...</td>\n",
       "      <td>1</td>\n",
       "      <td>20131011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>917056007</th>\n",
       "      <td>12515996043</td>\n",
       "      <td>50018831</td>\n",
       "      <td>21458:15841995;21956:3494076;27000458:59723383...</td>\n",
       "      <td>2</td>\n",
       "      <td>20141023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50008168</th>\n",
       "      <th>444069173</th>\n",
       "      <td>20487688075</td>\n",
       "      <td>50013636</td>\n",
       "      <td>21458:30992;13658074:3323064;1628665:3233941;1...</td>\n",
       "      <td>1</td>\n",
       "      <td>20141103</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     auction_id    cat_id  \\\n",
       "cat1     user_id                            \n",
       "50022520 786295544  41098319944  50014866   \n",
       "28       532110457  17916191097  50011993   \n",
       "50014815 249013725  21896936223  50012461   \n",
       "         917056007  12515996043  50018831   \n",
       "50008168 444069173  20487688075  50013636   \n",
       "\n",
       "                                                             property  \\\n",
       "cat1     user_id                                                        \n",
       "50022520 786295544  21458:86755362;13023209:3593274;10984217:21985...   \n",
       "28       532110457  21458:11399317;1628862:3251296;21475:137325;16...   \n",
       "50014815 249013725  21458:30992;1628665:92012;1628665:3233938;1628...   \n",
       "         917056007  21458:15841995;21956:3494076;27000458:59723383...   \n",
       "50008168 444069173  21458:30992;13658074:3323064;1628665:3233941;1...   \n",
       "\n",
       "                    buy_mount       day  \n",
       "cat1     user_id                         \n",
       "50022520 786295544          2  20140919  \n",
       "28       532110457          1  20131011  \n",
       "50014815 249013725          1  20131011  \n",
       "         917056007          2  20141023  \n",
       "50008168 444069173          1  20141103  "
      ]
     },
     "execution_count": 432,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "baby_trade.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 481,
   "id": "3558d02a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>auction_id</th>\n",
       "      <th>cat_id</th>\n",
       "      <th>property</th>\n",
       "      <th>buy_mount</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cat1</th>\n",
       "      <th>user_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <th>532110457</th>\n",
       "      <td>17916191097</td>\n",
       "      <td>50011993</td>\n",
       "      <td>21458:11399317;1628862:3251296;21475:137325;16...</td>\n",
       "      <td>1</td>\n",
       "      <td>20131011</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 auction_id    cat_id  \\\n",
       "cat1 user_id                            \n",
       "28   532110457  17916191097  50011993   \n",
       "\n",
       "                                                         property  buy_mount  \\\n",
       "cat1 user_id                                                                   \n",
       "28   532110457  21458:11399317;1628862:3251296;21475:137325;16...          1   \n",
       "\n",
       "                     day  \n",
       "cat1 user_id              \n",
       "28   532110457  20131011  "
      ]
     },
     "execution_count": 481,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "baby_trade.loc[28].head() #第一层引用\n",
    "baby_trade.loc[28][0:2]  #第二层引用\n",
    "# 直接引用两层\n",
    "baby_trade.loc[28,532110457,:]\n",
    "#baby_trade.loc[(28,532110457),'cat_id'] # 使用tuple\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 465,
   "id": "66094f65",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>auction_id</th>\n",
       "      <th>cat_id</th>\n",
       "      <th>property</th>\n",
       "      <th>buy_mount</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cat1</th>\n",
       "      <th>user_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">28</th>\n",
       "      <th>532110457</th>\n",
       "      <td>17916191097</td>\n",
       "      <td>50011993</td>\n",
       "      <td>21458:11399317;1628862:3251296;21475:137325;16...</td>\n",
       "      <td>1</td>\n",
       "      <td>20131011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82830661</th>\n",
       "      <td>19948600790</td>\n",
       "      <td>50013874</td>\n",
       "      <td>21458:11580;21475:137325</td>\n",
       "      <td>1</td>\n",
       "      <td>20121101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475046636</th>\n",
       "      <td>10368360710</td>\n",
       "      <td>203527</td>\n",
       "      <td>22724:40168;22729:40278;21458:21817;2770200:24...</td>\n",
       "      <td>1</td>\n",
       "      <td>20121101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>377550424</th>\n",
       "      <td>15771663914</td>\n",
       "      <td>50015841</td>\n",
       "      <td>1628665:3233941;1628665:3233942;3914866:11580;...</td>\n",
       "      <td>1</td>\n",
       "      <td>20121123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>530850018</th>\n",
       "      <td>22058239899</td>\n",
       "      <td>50024147</td>\n",
       "      <td>21458:205007542;43307470:5543413;2339128:62147...</td>\n",
       "      <td>1</td>\n",
       "      <td>20140210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">50014815</th>\n",
       "      <th>413188001</th>\n",
       "      <td>16521677358</td>\n",
       "      <td>50012478</td>\n",
       "      <td>21458:28155;5434803:3636603;2815901:22583732;1...</td>\n",
       "      <td>1</td>\n",
       "      <td>20130107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>641734831</th>\n",
       "      <td>22105131076</td>\n",
       "      <td>50014277</td>\n",
       "      <td>21458:21906;13227811:51479;13230966:75369014;3...</td>\n",
       "      <td>2</td>\n",
       "      <td>20141016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268356658</th>\n",
       "      <td>36932456353</td>\n",
       "      <td>50010236</td>\n",
       "      <td>21458:10513072;12474507:706291650;3091143:9208...</td>\n",
       "      <td>1</td>\n",
       "      <td>20141027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196272909</th>\n",
       "      <td>10066997901</td>\n",
       "      <td>50009540</td>\n",
       "      <td>21458:21906;13229910:32056435;2191928:73664723...</td>\n",
       "      <td>1</td>\n",
       "      <td>20141104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23473499</th>\n",
       "      <td>38019470815</td>\n",
       "      <td>50010236</td>\n",
       "      <td>1628665:61550;1628665:3233940;1628665:3233936;...</td>\n",
       "      <td>1</td>\n",
       "      <td>20141104</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11797 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     auction_id    cat_id  \\\n",
       "cat1     user_id                            \n",
       "28       532110457  17916191097  50011993   \n",
       "         82830661   19948600790  50013874   \n",
       "         475046636  10368360710    203527   \n",
       "         377550424  15771663914  50015841   \n",
       "         530850018  22058239899  50024147   \n",
       "...                         ...       ...   \n",
       "50014815 413188001  16521677358  50012478   \n",
       "         641734831  22105131076  50014277   \n",
       "         268356658  36932456353  50010236   \n",
       "         196272909  10066997901  50009540   \n",
       "         23473499   38019470815  50010236   \n",
       "\n",
       "                                                             property  \\\n",
       "cat1     user_id                                                        \n",
       "28       532110457  21458:11399317;1628862:3251296;21475:137325;16...   \n",
       "         82830661                            21458:11580;21475:137325   \n",
       "         475046636  22724:40168;22729:40278;21458:21817;2770200:24...   \n",
       "         377550424  1628665:3233941;1628665:3233942;3914866:11580;...   \n",
       "         530850018  21458:205007542;43307470:5543413;2339128:62147...   \n",
       "...                                                               ...   \n",
       "50014815 413188001  21458:28155;5434803:3636603;2815901:22583732;1...   \n",
       "         641734831  21458:21906;13227811:51479;13230966:75369014;3...   \n",
       "         268356658  21458:10513072;12474507:706291650;3091143:9208...   \n",
       "         196272909  21458:21906;13229910:32056435;2191928:73664723...   \n",
       "         23473499   1628665:61550;1628665:3233940;1628665:3233936;...   \n",
       "\n",
       "                    buy_mount       day  \n",
       "cat1     user_id                         \n",
       "28       532110457          1  20131011  \n",
       "         82830661           1  20121101  \n",
       "         475046636          1  20121101  \n",
       "         377550424          1  20121123  \n",
       "         530850018          1  20140210  \n",
       "...                       ...       ...  \n",
       "50014815 413188001          1  20130107  \n",
       "         641734831          2  20141016  \n",
       "         268356658          1  20141027  \n",
       "         196272909          1  20141104  \n",
       "         23473499           1  20141104  \n",
       "\n",
       "[11797 rows x 5 columns]"
      ]
     },
     "execution_count": 465,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#第一层引用 引用两个index\n",
    "baby_trade.loc[[28,50014815]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 470,
   "id": "48e4dd14",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>zs</th>\n",
       "      <th>ls</th>\n",
       "      <th>ww</th>\n",
       "      <th>zl</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">python</th>\n",
       "      <th>期中</th>\n",
       "      <td>14</td>\n",
       "      <td>118</td>\n",
       "      <td>101</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>期末</th>\n",
       "      <td>69</td>\n",
       "      <td>145</td>\n",
       "      <td>44</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">math</th>\n",
       "      <th>期中</th>\n",
       "      <td>82</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>期末</th>\n",
       "      <td>128</td>\n",
       "      <td>87</td>\n",
       "      <td>102</td>\n",
       "      <td>138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Chinese</th>\n",
       "      <th>期中</th>\n",
       "      <td>125</td>\n",
       "      <td>37</td>\n",
       "      <td>56</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>期末</th>\n",
       "      <td>127</td>\n",
       "      <td>125</td>\n",
       "      <td>127</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             zs   ls   ww   zl\n",
       "python  期中   14  118  101  140\n",
       "        期末   69  145   44   18\n",
       "math    期中   82    3    2   31\n",
       "        期末  128   87  102  138\n",
       "Chinese 期中  125   37   56   74\n",
       "        期末  127  125  127   10"
      ]
     },
     "execution_count": 470,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 创建dataframe, 加入多层索引\n",
    "df3 = pd.DataFrame(np.random.randint(0,150,size=(6,4)),\n",
    "               columns = ['zs','ls','ww','zl'],\n",
    "               index = \n",
    "                [['python','python','math','math','Chinese','Chinese'],\n",
    "                 ['期中','期末','期中','期末','期中','期末']])\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24cd8a0b",
   "metadata": {},
   "source": [
    "class1=['python','python','math','math','Chinese','Chinese']\n",
    "class2=['期中','期末','期中','期末','期中','期末']\n",
    "m_index = pd.MultiIndex.from_arrays([class1,class2]) \n",
    "df3 = pd.DataFrame(np.random.randint(0,150,(6,4)),index=m_index) \n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 474,
   "id": "bed5ff2e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">python</th>\n",
       "      <th>期中</th>\n",
       "      <td>97</td>\n",
       "      <td>91</td>\n",
       "      <td>59</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>期末</th>\n",
       "      <td>13</td>\n",
       "      <td>74</td>\n",
       "      <td>34</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">math</th>\n",
       "      <th>期中</th>\n",
       "      <td>133</td>\n",
       "      <td>65</td>\n",
       "      <td>80</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>期末</th>\n",
       "      <td>88</td>\n",
       "      <td>58</td>\n",
       "      <td>59</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Chinses</th>\n",
       "      <th>期中</th>\n",
       "      <td>115</td>\n",
       "      <td>26</td>\n",
       "      <td>138</td>\n",
       "      <td>122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>期末</th>\n",
       "      <td>60</td>\n",
       "      <td>11</td>\n",
       "      <td>14</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              0   1    2    3\n",
       "python  期中   97  91   59   69\n",
       "        期末   13  74   34   29\n",
       "math    期中  133  65   80   44\n",
       "        期末   88  58   59   62\n",
       "Chinses 期中  115  26  138  122\n",
       "        期末   60  11   14   16"
      ]
     },
     "execution_count": 474,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "class1=['python','math','Chinses']\n",
    "class2=['期中','期末']\n",
    "m_index = pd.MultiIndex.from_product([class1,class2]) \n",
    "df3 = pd.DataFrame(np.random.randint(0,150,(6,4)),index=m_index) \n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 483,
   "id": "05e1a2c7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "97"
      ]
     },
     "execution_count": 483,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.loc[('python','期中'),0]"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
